<?php
class product_plugin_specials extends abstract_product_plugin {

    public function getProductSpecials($data = array()) {

        $sql = "SELECT DISTINCT *, 
            (SELECT AVG(rating) 
            FROM " . $this->db_prefix . "review r1 
            WHERE r1.product_id = ps.product_id AND r1.status = '1' 
            GROUP BY r1.product_id) AS rating 

            FROM " . $this->db_prefix . "product_special ps 

            LEFT JOIN " . $this->db_prefix . "product p 
            ON (ps.product_id = p.product_id) 

            LEFT JOIN " . $this->db_prefix . "product_description pd 
            ON (p.product_id = pd.product_id) 

            LEFT JOIN " . $this->db_prefix . "product_to_store p2s 
            ON (p.product_id = p2s.product_id) 

            WHERE p.status = '1' AND p.date_available <= NOW() 
            AND p2s.store_id = :store_id  
            AND ps.user_group_id = :user_group_id 
            AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) 
            AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) 

            GROUP BY ps.product_id";

        $sort_data = array(
            'pd.name',
            'p.model',
            'ps.price',
            'rating',
            'p.sort_order'
        );

        if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
            if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
                $sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
            } else {
                $sql .= " ORDER BY " . $data['sort'];
            }
        } else {
            $sql .= " ORDER BY p.sort_order";	
        }

        if (isset($data['order']) && ($data['order'] == 'DESC')) {
            $sql .= " DESC, LCASE(pd.name) DESC";
        } else {
            $sql .= " ASC, LCASE(pd.name) ASC";
        }

        if (isset($data['start']) || isset($data['limit'])) {
            if ($data['start'] < 0) {
                $data['start'] = 0;
            }				

            if ($data['limit'] < 1) {
                $data['limit'] = 20;
            }	

            $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
        }

        $params = array(
            ':user_group_id'    => (int) $this->user_group_id,
            ':store_id'             => (int) $this->site_id
        );

        $product_data = array();

        $query = $this->pdo->prepare($sql);
        $query->execute($params);

        while ($product = $query->fetchObject('class_product')) {
            $product_data[$product->get_id()] = $product;	
        }

        return $product_data;
    }
}	
